﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace QUAN_LY_MUON_TRA_SACH
{
    public partial class frmbao_cao : Form
    {
        public frmbao_cao()
        {
            InitializeComponent();
        }

        private void frmbc_luotmuon_theotungquyen_Load(object sender, EventArgs e)
        {
            if (FORM_MAIN.frmmain.lblbaocao.Text == "BÁO CÁO LƯỢT MƯỢN \nTHEO TỪNG QUYỂN")
            {
                try
                {
                    DataTable bang1 = new DataTable();
                    SqlDataAdapter lenh = new SqlDataAdapter("select c.ISBN,c.STT_CUON,count(C.STT_PHIEU) AS SO_LUOT_MUON INTO #TAM from dau_sach a,cuon_sach b,gom_cuon c,phieu_muon e where a.ISBN=b.ISBN and b.ISBN=c.ISBN and b.stt_cuon=c.stt_cuon and c.stt_phieu=e.stt_phieu and ngay_gio_muon>='" + FORM_MAIN.frmmain.cadtungay.Text + "' and ngay_gio_muon<='" + FORM_MAIN.frmmain.caddenngay.Text + "' group by c.ISBN,c.STT_CUON order by SO_LUOT_MUON DESC select #tam.ISBN,#tam.STT_CUON,TUASACH,TENNXB,NAMXB,#tam.SO_LUOT_MUON from #tam,dau_sach b,nha_xuat_ban c where #tam.ISBN=b.ISBN and b.ma_nxb=c.ma_nxb drop table #tam", Connection.sqlConnection);
                    lenh.Fill(bang1);
                    rpt_luot_muon_theo_tung_quyen bc1 = new rpt_luot_muon_theo_tung_quyen();
                    bc1.DataSource = bang1;
                    bc1.BindData();
                    SqlCommand lenh1 = new SqlCommand("select c.ISBN,c.STT_CUON,count(C.STT_PHIEU) AS SO_LUOT_MUON INTO #TAM from dau_sach a,cuon_sach b,gom_cuon c,phieu_muon e where a.ISBN=b.ISBN and b.ISBN=c.ISBN and b.stt_cuon=c.stt_cuon and c.stt_phieu=e.stt_phieu and ngay_gio_muon>='" + FORM_MAIN.frmmain.cadtungay.Text + "' and ngay_gio_muon<='" + FORM_MAIN.frmmain.caddenngay.Text + "' group by c.ISBN,c.STT_CUON order by SO_LUOT_MUON DESC select sum(SO_LUOT_MUON) from #tam,dau_sach b,nha_xuat_ban c where #tam.ISBN=b.ISBN and b.ma_nxb=c.ma_nxb drop table #tam", Connection.sqlConnection);
                    bc1.colso_lm.Text = Convert.ToString(lenh1.ExecuteScalar());
                    printControl1.PrintingSystem = bc1.PrintingSystem;
                    bc1.CreateDocument();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else if (FORM_MAIN.frmmain.lblbaocao.Text == "BÁO CÁO LƯỢT MƯỢN \nTHEO TỪNG THỂ LOẠI")
            {
                try
                {
                    DataTable bang1 = new DataTable();
                    SqlDataAdapter lenh = new SqlDataAdapter("select TEN_TLOAI,count(C.STT_PHIEU) AS SO_LUOT_MUON from dau_sach a,cuon_sach b,gom_cuon c,phieu_muon d,the_loai e where a.ISBN=b.ISBN and b.ISBN=c.ISBN and b.stt_cuon=c.stt_cuon and c.stt_phieu=d.stt_phieu and a.ma_tloai=e.ma_tloai and ngay_gio_muon>='" + FORM_MAIN.frmmain.cadtungay.Text + "' and ngay_gio_muon<='" + FORM_MAIN.frmmain.caddenngay.Text + "' group by e.ma_tloai,ten_tloai order by e.ma_tloai", Connection.sqlConnection);
                    lenh.Fill(bang1);
                    rptluot_muon_theo_the_loai bc1 = new rptluot_muon_theo_the_loai();
                    bc1.DataSource = bang1;
                    bc1.BindData();
                    SqlCommand lenh1 = new SqlCommand("select TEN_TLOAI,count(C.STT_PHIEU) AS SO_LUOT_MUON into #tam from dau_sach a,cuon_sach b,gom_cuon c,phieu_muon d,the_loai e where a.ISBN=b.ISBN and b.ISBN=c.ISBN and b.stt_cuon=c.stt_cuon and c.stt_phieu=d.stt_phieu and a.ma_tloai=e.ma_tloai and ngay_gio_muon>='" + FORM_MAIN.frmmain.cadtungay.Text + "' and ngay_gio_muon<='" + FORM_MAIN.frmmain.caddenngay.Text + "' group by e.ma_tloai,ten_tloai order by e.ma_tloai select sum(SO_LUOT_MUON) from #tam drop table #tam", Connection.sqlConnection);
                    bc1.colso_lm.Text = Convert.ToString(lenh1.ExecuteScalar());
                    printControl1.PrintingSystem = bc1.PrintingSystem;
                    bc1.CreateDocument();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else if (FORM_MAIN.frmmain.lblbaocao.Text == "DANH SÁCH ĐỌC GIẢ \n MƯỢN QUÁ HẠN")
            {
                try
                {
                    DataTable bang1 = new DataTable();
                    SqlDataAdapter lenh = new SqlDataAdapter("select HTEN_DG,C.MATHE,DGIAI,TEN_TRG,COUNT(*) AS SO_SACH_QUA_HAN from gom_cuon a,phieu_muon b,the_doc_gia c,loai_doc_gia d,nganh e,khoa f,truong g where a.stt_phieu=b.stt_phieu and b.mathe=c.mathe and c.ma_loai_dgia=d.ma_loai_dgia and c.ma_ng=e.ma_ng and e.makhoa=f.makhoa and f.ma_trg=g.ma_trg and getdate()>ngay_het_han and ngay_gio_muon>='" + FORM_MAIN.frmmain.cadtungay.Text + "' and ngay_gio_muon<='" + FORM_MAIN.frmmain.caddenngay.Text + "' group by c.mathe,hten_dg,c.ma_loai_dgia,dgiai,g.ma_trg,ten_trg", Connection.sqlConnection);
                    lenh.Fill(bang1);
                    rpt_ds_doc_gia_tra_qua_han bc1 = new rpt_ds_doc_gia_tra_qua_han();
                    bc1.DataSource = bang1;
                    bc1.BindData();
                    SqlCommand lenh1 = new SqlCommand("select HTEN_DG,C.MATHE,DGIAI,TEN_TRG,COUNT(*) AS SO_SACH_QUA_HAN into #tam from gom_cuon a,phieu_muon b,the_doc_gia c,loai_doc_gia d,nganh e,khoa f,truong g where a.stt_phieu=b.stt_phieu and b.mathe=c.mathe and c.ma_loai_dgia=d.ma_loai_dgia and c.ma_ng=e.ma_ng and e.makhoa=f.makhoa and f.ma_trg=g.ma_trg and getdate()>ngay_het_han and ngay_gio_muon>='" + FORM_MAIN.frmmain.cadtungay.Text + "' and ngay_gio_muon<='" + FORM_MAIN.frmmain.caddenngay.Text + "' group by c.mathe,hten_dg,c.ma_loai_dgia,dgiai,g.ma_trg,ten_trg select sum(SO_SACH_QUA_HAN) from #tam drop table #tam", Connection.sqlConnection);
                    bc1.colsosachquahan.Text = Convert.ToString(lenh1.ExecuteScalar());
                    printControl1.PrintingSystem = bc1.PrintingSystem;
                    bc1.CreateDocument();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else if (FORM_MAIN.frmmain.lblbaocao.Text == "DANH SÁCH ĐỌC GIẢ \nBỊ PHẠT")
            {
                try
                {
                    DataTable bang1 = new DataTable();
                    SqlDataAdapter lenh = new SqlDataAdapter("select HTEN_DG,C.MATHE,RTRIM(CAST((DATEPART(DAY,NGAYLAP)) AS CHAR(5)))+'/'+RTRIM(CAST((DATEPART(MONTH,NGAYLAP)) AS CHAR(5)))+'/'+CAST((DATEPART(YEAR,NGAYLAP)) AS CHAR(5)) AS NGAYLAP,LY_DO,SOTIEN_PHAT from phieu_phat a,phieu_muon b,the_doc_gia c where a.stt_phieu=b.stt_phieu and b.mathe=c.mathe and ngaylap>='" + FORM_MAIN.frmmain.cadtungay.Text + "' and ngaylap<='" + FORM_MAIN.frmmain.caddenngay.Text + "'", Connection.sqlConnection);
                    lenh.Fill(bang1);
                    rpt_ds_doc_gia_bi_phat bc1 = new rpt_ds_doc_gia_bi_phat();
                    bc1.DataSource = bang1;
                    bc1.BindData();
                    SqlCommand lenh1 = new SqlCommand("select HTEN_DG,C.MATHE,NGAYLAP,LY_DO,SOTIEN_PHAT into #tam from phieu_phat a,phieu_muon b,the_doc_gia c where a.stt_phieu=b.stt_phieu and b.mathe=c.mathe and ngaylap>='" + FORM_MAIN.frmmain.cadtungay.Text + "' and ngaylap<='" + FORM_MAIN.frmmain.caddenngay.Text + "' select sum(SOTIEN_PHAT) from #tam drop table #tam", Connection.sqlConnection);
                    bc1.coltongtienphat.Text = Convert.ToString(lenh1.ExecuteScalar());
                    printControl1.PrintingSystem = bc1.PrintingSystem;
                    bc1.CreateDocument();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else if (FORM_MAIN.frmmain.lblbaocao.Text == "BÁO CÁO LƯỢT MƯỢN \nTHEO TỪNG NĂM")
            {
                try
                {
                    DataTable bang1 = new DataTable();
                    SqlDataAdapter lenh = new SqlDataAdapter("select year(ngay_gio_muon) as NAM,count(*) as SO_LUOT_MUON from phieu_muon a,gom_cuon b where a.stt_phieu=b.stt_phieu and year(ngay_gio_muon)>=" + Convert.ToInt32(FORM_MAIN.frmmain.txttunam.Text) + " and year(ngay_gio_muon)<=" + Convert.ToInt32(FORM_MAIN.frmmain.txtdennam.Text) + " group by year(ngay_gio_muon)", Connection.sqlConnection);
                    lenh.Fill(bang1);
                    rpt_luot_muon_theo_nam bc1 = new rpt_luot_muon_theo_nam();
                    bc1.DataSource = bang1;
                    bc1.BindData();
                    SqlCommand lenh1 = new SqlCommand("select year(ngay_gio_muon) as NAM,count(*) as SO_LUOT_MUON into #tam from phieu_muon a,gom_cuon b where a.stt_phieu=b.stt_phieu and year(ngay_gio_muon)>=" + Convert.ToInt32(FORM_MAIN.frmmain.txttunam.Text) + " and year(ngay_gio_muon)<=" + Convert.ToInt32(FORM_MAIN.frmmain.txtdennam.Text) + " group by year(ngay_gio_muon) select sum(SO_LUOT_MUON) from #tam drop table #tam", Connection.sqlConnection);
                    bc1.colso_lm.Text = Convert.ToString(lenh1.ExecuteScalar());
                    printControl1.PrintingSystem = bc1.PrintingSystem;
                    bc1.CreateDocument();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
    }
}
